/**
 Ceated by Amit Mudgal (MicroStrategy Principal Designer) for GMS on 6/20/2014
 *
 */
package com.amex.SQL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.amex.SQL.debug.DebugSQL;
import com.amex.uam.pojo.Country;
import com.amex.uam.pojo.FUNC_GRP;
import com.amex.uam.pojo.FUNC_GRP_ROLE;
import com.amex.uam.pojo.MS_USER_RGN_ACSS;
import com.amex.uam.pojo.Region;
import com.amex.uam.tasks.MS_USER_CTRY_ACSS;
import com.amex.uam.tasks.UserProfile;
import com.google.gson.Gson;

public class DBServiceHandler {

	private static final String QUERY_FUNCTIONAL_GROUPS_SQL = "SELECT FUNC_GRP_ID,FUNC_GRP_NM FROM FUNC_GRP";
	private static final String QUERY_FUNCTIONAL_ROLE_SQL = "select FUNC_GRP_ROLE_ID,FUNC_GRP_ID,FUNC_GRP_ROLE_NM from FUNC_GRP_ROLE";	
	private static final String WRITE_TO_NZ = "INSERT INTO MS_USER_ADMIN ("
			+ "WORKFORCE_MBR_ID,"
			+ "USER_ADMIN_STA_CD,"
			+ "USER_GRP_ID,"
			+ "USER_STA_CD,"
			+ "USER_TYPE_CD,"
			+ "COST_CTR_CD,"
			+ "DEPT_ID,"
			+ "AMEX_BUS_CD,"
			+ "USER_ACSS_TYPE_CD,"
			+ "USER_ID,"
			+ "USER_1ST_NM,"
			+ "USER_MI_TX,"
			+ "USER_LST_NM," 
			+ "USER_EMAIL_AD_TX,"
			+ "USER_CMPL_PHONE_NO,"
			+ "WORKFORCE_MBR_MAN_1ST_NM,"
			+ "WORKFORCE_MBR_MAN_MI_TX,"
			+ "WORKFORCE_MBR_MAN_LST_NM,"
			+ "WORKFORCE_MBR_MAN_EMAIL_AD_TX,"
			+ "VP_NM,"
			+ "ACSS_RSN_TX,"
			+ "USER_ACSS_REJCT_RSN_TX,"
			+ "WORKFORCE_MBR_CLASS_CD,"
			+ "AMEX_UNIT_CD,"
			+ "LST_LOGIN_TS,"
			+ "CREAT_TS,"
			+ "LST_UPDT_TS,"
			+ "GEO_ACSS_LVL_CD"
			+ ")"
			+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	public static String getFunctionalGroups(){
		Connection conn = DBOperations.getConnection();
		
		//Connection conn = QueryExecution.SQLconnect(sqlHost, sqlDB, sqlUID, ecrypt.Decrypt(sqlepwd), sqlDriver, sqlconurl, sqlport);
		//ResultSet rs = QueryExecution.ExecSQLQuery(conn, sql.replaceAll("REPLACESTRING1", Uid)); 
		
        PreparedStatement pstat = null;
        String XML ="";
        if (conn != null) {
			try {
				//pstat = conn.prepareStatement(QUERY_FUNCTIONAL_GROUPS_SQL);
				ResultSet rs = ExecSQLQuery(conn,QUERY_FUNCTIONAL_GROUPS_SQL);
				
				while(rs.next()){
					XML += "<indexgrp id=\""+rs.getString("FUNC_GRP_ID") + "\">" + "<functionGrp>"+ rs.getString("FUNC_GRP_NM") + "," + rs.getString("FUNC_GRP_ID")+ "</functionGrp></indexgrp>";
				}
				System.out.println(XML);
			}catch(Exception e){}
		}
		DBOperations.disconnect(conn, pstat);
		return XML;

	}
	
	public static String getFunctionalRole(){
		Connection conn = DBOperations.getConnection();
		
		
        PreparedStatement pstat = null;
        String XML ="";
        if (conn != null) {
			try {
				//pstat = conn.prepareStatement(QUERY_FUNCTIONAL_GROUPS_SQL);
				ResultSet rs = ExecSQLQuery(conn,QUERY_FUNCTIONAL_ROLE_SQL);
				
				while(rs.next()){
					XML += "<indexrole id=\""+rs.getString("FUNC_GRP_ROLE_ID") + "\">" + "<functionRole>"+ rs.getString("FUNC_GRP_ROLE_NM").replaceFirst("\\s+$", "").replaceAll("&","&amp;") + "," + rs.getString("FUNC_GRP_ROLE_ID")+ "," + rs.getString("FUNC_GRP_ID") +"</functionRole></indexrole>";
				}
				System.out.println(XML);
			}catch(Exception e){}
		}
		DBOperations.disconnect(conn, pstat);
		return XML;

	}
		
	public static boolean WritetoDBPreApprovedRegistration(UserProfile up)
	{
		System.out.println("Is up null ?" + up);
		if (up == null) return false;
		Connection conn = DBOperations.getConnection();
		System.out.println("DB connection formed" + conn);
		PreparedStatement pstat = null;
		if (conn != null) {
		try {
			pstat = conn.prepareStatement(WRITE_TO_NZ);
	
			pstat.setString(1, up.getWORKFORCE_MBR_ID());
			pstat.setString(2, up.getUSER_ADMIN_STA_CD());
			pstat.setInt(3, up.getUSER_GRP_ID());
			pstat.setString(4, up.getUSER_STA_CD());
			pstat.setString(5, up.getUSER_TYPE_CD());
			pstat.setString(6, up.getCOST_CTR_CD());
			pstat.setString(7, up.getDEPT_ID());
			pstat.setString(8, up.getAMEX_BUS_CD());
			pstat.setString(9, up.getUSER_ACSS_TYPE_CD());
			pstat.setString(10, up.getUSER_ID());
			pstat.setString(11, up.getUSER_1ST_NM());
			pstat.setString(12, up.getUSER_MI_TX());
			pstat.setString(13, up.getUSER_LST_NM()); 
			pstat.setString(14, up.getUSER_EMAIL_AD_TX());
			pstat.setString(15, up.getUSER_CMPL_PHONE_NO());
			pstat.setString(16, up.getWORKFORCE_MBR_MAN_1ST_NM());
			pstat.setString(17, up.getWORKFORCE_MBR_MAN_MI_TX());
			pstat.setString(18, up.getWORKFORCE_MBR_MAN_LST_NM());
			pstat.setString(19, up.getWORKFORCE_MBR_MAN_EMAIL_AD_TX());
			pstat.setString(20, up.getVP_NM());
			pstat.setString(21, up.getACSS_RSN_TX());
			pstat.setString(22, up.getUSER_ACSS_REJCT_RSN_TX());
			pstat.setString(23, up.getWORKFORCE_MBR_CLASS_CD());
			pstat.setString(24, up.getAMEX_UNIT_CD());
			pstat.setTimestamp(25, up.getLST_LOGIN_TS());
			pstat.setTimestamp(26, up.getCREAT_TS());
			pstat.setTimestamp(27, up.getLST_UPDT_TS());
			pstat.setString(28, up.getGEO_ACSS_LVL_CD());
			
			int rowCount = pstat.executeUpdate();
			
			return rowCount > 0;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			// release connection
			DBOperations.disconnect(conn, pstat);
		}
		
	}
		
		return true;
	}
	
	public static ResultSet ExecSQLQuery(Connection conn, String SQL) {
		
		ResultSet rs = null;
		
			System.out.println(SQL);
		
		try {
		  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		  rs = stmt.executeQuery(SQL);
		} catch (Exception e) {System.out.println(e);
		}
		return rs;
	}
	
	private static String SQL_GET_USER_PROFILE = DebugSQL.DEBUG ? DebugSQL.SQL_GET_USER_PROFILE :
		"SELECT	A.USER_STA_CD,A.USER_TYPE_CD,A.USER_ADMIN_STA_CD,A.USER_ACSS_TYPE_CD,A.WORKFORCE_MBR_ID,A.USER_1ST_NM, I.FUNC_GRP_ROLE_ID,"
		+ "	A.USER_MI_TX,A.USER_LST_NM,	A.DEPT_ID,A.AMEX_BUS_CD,A.COST_CTR_CD,A.USER_CMPL_PHONE_NO,	A.USER_EMAIL_AD_TX,"
		+ "	A.WORKFORCE_MBR_MAN_1ST_NM,	A.WORKFORCE_MBR_MAN_MI_TX,	A.WORKFORCE_MBR_MAN_LST_NM,	A.WORKFORCE_MBR_MAN_EMAIL_AD_TX,"
		+ "	A.VP_NM,A.ACSS_RSN_TX,	DAYS_BETWEEN(CURRENT_TIMESTAMP,A.NDA_TS)+1 as DAYS_DIFF,DATE(A.SEC_XCPT_END_DT) as SEC_XCPT_END_DT,"
		+ "	DATE(A.NDA_TS) as NDA_TS,	A.AMEX_UNIT_CD,	D.DEPT_NM,E.AMEX_BUS_DS_TX,	F.AMEX_UNIT_DS_TX,G.FUNC_GRP_ROLE_NM,	A.CLM_GLBL_ACS_IN,"
		+ "	A.LIF_GLBL_ACS_IN"
		+ " FROM	"
		+ "		MS_USER_ADMIN A,"
		+ "		USER_DEPT D,"
		+ "		AMEX_BUSINESS E,"
		+ "		AMEX_UNIT F, "
		+ "		MS_USER_FUNC_GRP_ROLE I, "
		+ "		FUNC_GRP_ROLE G"
		+ " WHERE	"
		+ "		A.WORKFORCE_MBR_ID= I.WORKFORCE_MBR_ID "
		+ "		AND A.USER_ID=? "
		+ "		AND	A.DEPT_ID=D.DEPT_ID "
		+ "		AND	A.AMEX_BUS_CD=E.AMEX_BUS_CD "
		+ "		AND	A.AMEX_UNIT_CD=F.AMEX_UNIT_CD "
		+ "		AND I.FUNC_GRP_ROLE_ID = G.FUNC_GRP_ROLE_ID";
	/**
	 * Get user profile
	 */
	public static Map<String, Object> getUserProfile(String userId) {
		
		if (userId == null || userId.equals("")) return null;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;

		if (conn == null) return null;
		
		try {
			
			Map<String, Object> up = null;
			
			pstat = conn.prepareStatement(SQL_GET_USER_PROFILE);
			pstat.setString(1, userId);
			res = pstat.executeQuery();

			if (res.next()) {
				up = new HashMap<String, Object>();
				ResultSetMetaData meta = res.getMetaData();
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					Object val = res.getObject(i);
					if (meta.getColumnClassName(i).equals("java.lang.String")) {
						val = Utils.trim((String) val);
					}
					up.put(meta.getColumnName(i), val);
				}
			}
			
			return up;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
		
	}

	private static final String SQL_FUNC_GRP = "SELECT FUNC_GRP_ID,FUNC_GRP_NM FROM FUNC_GRP";
	
	public static List<FUNC_GRP> loadFuncGroups() {
		
		Connection conn = DBOperations.getConnection();
		Statement stat = null;
		ResultSet res = null;
		
		if(conn == null) return null;
		
		try {
			List<FUNC_GRP> list = new ArrayList<FUNC_GRP>();
			
			stat = conn.createStatement();
			res = stat.executeQuery(SQL_FUNC_GRP);
			
			while (res.next()) {
				FUNC_GRP grp  = new FUNC_GRP();
				grp.setFUNC_GRP_ID(res.getInt("FUNC_GRP_ID"));
				grp.setFUNC_GRP_NM(res.getString("FUNC_GRP_NM"));
				list.add(grp);
			}

			return list;
			
		}catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, stat, res);
		}
		
	}
	/*get function role table*/
	private static String SQL_FUNCTIONAL_ROLE = "select A.FUNC_GRP_ROLE_ID, A.FUNC_GRP_ID, A.FUNC_GRP_ROLE_NM from FUNC_GRP_ROLE A";
	public static List<FUNC_GRP_ROLE> loadFuncRoles(){
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;

		if (conn == null){
			return null;
		}
		List<FUNC_GRP_ROLE> list = new ArrayList<FUNC_GRP_ROLE>();
		try {
			pstat = conn.prepareStatement(SQL_FUNCTIONAL_ROLE);
			res = pstat.executeQuery();
			while (res.next()) {
				FUNC_GRP_ROLE map = new FUNC_GRP_ROLE();
				map.setFUNC_GRP_ROLE_ID(res.getInt("FUNC_GRP_ROLE_ID"));
				map.setFUNC_GRP_ID(res.getInt("FUNC_GRP_ID"));
				map.setFUNC_GRP_ROLE_NM(res.getString("FUNC_GRP_ROLE_NM"));
				list.add(map);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
	}
	
	private static String LOAD_COUNTRY_BY_USER = "select CTRY_NM from MS_USER_CTRY_ACSS where WORKFORCE_MBR_ID = ?";
	
	public static List<MS_USER_CTRY_ACSS> loadCountryByUser(String amexWebId) {

		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;

		if (conn == null) {
			return null;
		}
		List<MS_USER_CTRY_ACSS> list = new ArrayList<MS_USER_CTRY_ACSS>();
		try {
			pstat = conn.prepareStatement(LOAD_COUNTRY_BY_USER);
			pstat.setString(1, amexWebId);
			res = pstat.executeQuery();
			while (res.next()) {
				MS_USER_CTRY_ACSS bean = new MS_USER_CTRY_ACSS();
				bean.setCTRY_NM(res.getString("CTRY_NM"));
				//bean.setWORKFORCE_MBR_ID(res.getString("WORKFORCE_MBR_ID"));
				//bean.setCREAT_TS(res.getTimestamp("CREAT_TS"));
				list.add(bean);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
	}
	
	private static String LOAD_REGION_BY_USER = "select RGN_ABBR_NM from MS_USER_RGN_ACSS where WORKFORCE_MBR_ID = ?";
	public static List<MS_USER_RGN_ACSS> loadRegionByUser(String amexWebId) {

		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;

		if (conn == null) {
			return null;
		}
		List<MS_USER_RGN_ACSS> list = new ArrayList<MS_USER_RGN_ACSS>();
		try {
			pstat = conn.prepareStatement(LOAD_REGION_BY_USER);
			pstat.setString(1, amexWebId);
			res = pstat.executeQuery();
			while (res.next()) {
				MS_USER_RGN_ACSS bean = new MS_USER_RGN_ACSS();
				bean.setRGN_ABBR_NM(res.getString("RGN_ABBR_NM"));
				//bean.setWORKFORCE_MBR_ID(res.getString("WORKFORCE_MBR_ID"));
				//bean.setCREAT_TS(res.getTimestamp("CREAT_TS"));
				list.add(bean);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
	}
	
	private static String COUNTRY_ID_NAME = "select C.IDENTIFIER_ID, C.NAME_NM from COUNTRY C";
	public static List<Country> getCountryIdName(){
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Country> countryList = new ArrayList<Country>();
		if(conn==null){
			return null;
		}
		try {
			pstat = conn.prepareStatement(COUNTRY_ID_NAME);
			rs = pstat.executeQuery();
			
			while(rs.next()){
				Country c = new Country();
				c.setIDENTIFIER_ID(rs.getString("IDENTIFIER_ID"));
				c.setNAME_NM(rs.getString("NAME_NM"));
				countryList.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBOperations.disconnect(conn);
		}
		return countryList;
	}
	private static String REGION_ID_NAME = "select RGN_ID, ABBR_NM from REGION R where R.FK_CTRYCTRY_ID = ?";
	public static List<Region> getRegionIdName(String countryId){
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Region> regionList = new ArrayList<Region>();
		if(conn==null){
			return null;
		}
		try {
			pstat = conn.prepareStatement(REGION_ID_NAME);
			pstat.setString(1, countryId);
			rs = pstat.executeQuery();
			
			while(rs.next()){
				Region r = new Region();
				r.setRGN_ID(rs.getString("RGN_ID"));
				r.setABBR_NM(rs.getString("ABBR_NM"));
				regionList.add(r);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBOperations.disconnect(conn);
		}
		return regionList;
	}
	
	public static void main(String[] args) {
		String userId = "8165840";
		String amexWebId = "amudga";                        
		Gson gson = new Gson();
		System.out.println(gson.toJson(loadRegionByUser(amexWebId)));
	}
	
}

